package com.wedo.mall.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.wedo.mall.entity.Account;
import com.wedo.mall.entity.History;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 简单的SQLite，目前仅考虑存搜索历史记录和登录记录
 * Created by e on 2017/7/24.
 */

public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "wedo_db";

    private static final int DB_VERSION = 3;
    private static final String DB_NAME = "wedo.db";
    public static final String TABLE_SEARCH_HISTORY = "search_history";
    private static final String TABLE_ACCOUNT_HISTORY = "account_history";


    private Map<String,String> table = new HashMap<>(2);

    private static DBHelper instance;
    public static DBHelper with(Context context){
        if (instance == null){
            synchronized (DBHelper.class){
                if (instance == null){
                    instance = new DBHelper(context);
                }
            }
        }

        return instance;
    }

    private DBHelper(Context context){
        super(context,DB_NAME,null,DB_VERSION);

        table.put(TABLE_SEARCH_HISTORY,"(id integer primary key AUTOINCREMENT, keyword text, time long)");
        table.put(TABLE_ACCOUNT_HISTORY,"(id integer primary key AUTOINCREMENT, username text, password text,headimgurl text)");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Set<String> keys = table.keySet();
        for (String key : keys) {
            StringBuilder sql = new StringBuilder();
            sql.append("create table if not exists ");
            sql.append(key);
            sql.append(table.get(key));

            db.execSQL(sql.toString());
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Set<String> keys = table.keySet();
        for (String key : keys) {
            StringBuilder sql = new StringBuilder();
            sql.append("DROP TABLE IF EXISTS ").append(key);
            db.execSQL(sql.toString());
        }

        onCreate(db);
    }

    /**查询历史搜索记录，仅查询前10条*/
    public List<History> histories(){

        SQLiteDatabase db = getReadableDatabase();

        StringBuilder sql = new StringBuilder();
        sql.append("select * from ").append(TABLE_SEARCH_HISTORY);
        sql.append(" order by time desc limit 0,10");

        Cursor cursor = db.rawQuery(sql.toString(),null);
        if (cursor !=null){
            List<History> histories = new ArrayList<>(10);
            while (cursor.moveToNext()){
                History history = new History();
                history.id = cursor.getInt(0);
                history.keyword = cursor.getString(1);
                history.time = cursor.getLong(2);
                histories.add(history);
            }

            cursor.close();
            return histories;
        }

        return new ArrayList<>(0);
    }

    public void insert(String keyword){
        try {
            SQLiteDatabase db = getWritableDatabase();
            if(db.isDbLockedByCurrentThread()) {
                insert(db,keyword);
            }else {
                db.beginTransaction();
                try {
                    insert(db,keyword);
                    db.setTransactionSuccessful();
                }finally {
                    db.endTransaction();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**将历史记录维持在最多十条数据*/
    private void insert(SQLiteDatabase db,String keyword){

        StringBuilder query = new StringBuilder();
        query.append("select * from ").append(TABLE_SEARCH_HISTORY).append(" where keyword = ?");
        Cursor cursor = db.rawQuery(query.toString(),new String[]{keyword});
        if(cursor.moveToFirst()) {
            int id = cursor.getInt(0);
            cursor.close();
            /**如果该关键字已存在，更新搜索日期*/
            ContentValues cv = new ContentValues();
            cv.put("keyword",keyword);
            cv.put("time",System.currentTimeMillis());
            int update = db.update(TABLE_SEARCH_HISTORY,cv,"id=?",new String[]{String.valueOf(id)});
            if(update != -1) {
                Log.i(TAG,"update "+keyword +" success");
            }
        }else {
            cursor.close();
            /**新增*/
            ContentValues cv = new ContentValues();
            cv.put("keyword",keyword);
            cv.put("time",System.currentTimeMillis());
            long insert = db.insert(TABLE_SEARCH_HISTORY,null,cv);
            if(insert !=-1) {
                Log.i(TAG,"insert "+insert +" success");
            }

            /**新增成功，删除第十条以后的记录*/
            StringBuilder sql = new StringBuilder();
            sql.append("select * from ").append(TABLE_SEARCH_HISTORY);
            sql.append(" order by time desc");
            Cursor all = db.rawQuery(sql.toString(),null);
            if(all.moveToPosition(9)) {
                long time = all.getLong(2);
                int delete = db.delete(TABLE_SEARCH_HISTORY,"time < ?",new String[]{String.valueOf(time)});
                if(delete !=-1) {
                    Log.i(TAG,"delete "+delete +" success");
                }

            }
            all.close();
        }
    }

    public void update(History history){
        try {
            SQLiteDatabase db = getWritableDatabase();
            if(db.isDbLockedByCurrentThread()) {
                update(db,history);
            }else {
                db.beginTransaction();
                try {
                    update(db,history);
                    db.setTransactionSuccessful();
                }finally {
                    db.endTransaction();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private void update(SQLiteDatabase db,History history){
        ContentValues cv = new ContentValues();
        cv.put("keyword",history.keyword);
        cv.put("time",System.currentTimeMillis());
        db.update(TABLE_SEARCH_HISTORY,cv,"id=?",new String[]{String.valueOf(history.id)});
    }

    /**退出登录，清空历史搜索记录*/
    public void clear(){
        StringBuilder sql = new StringBuilder();
        sql.append("delete from ").append(TABLE_SEARCH_HISTORY);
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL(sql.toString());
    }


    /***登录成功，记住登录账号和密码*/
    public void onLoginSuccessful(String username, String password,String img){
        try {
            SQLiteDatabase db = getWritableDatabase();
            if(db.isDbLockedByCurrentThread()) {
                onLoginSuccessful(db,username,password,img);
            }else {
                db.beginTransaction();
                try {
                    onLoginSuccessful(db,username,password,img);
                    db.setTransactionSuccessful();
                }finally {
                    db.endTransaction();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private void onLoginSuccessful(SQLiteDatabase db, String username, String password,String img){
        StringBuilder query = new StringBuilder();
        query.append("select * from ").append(TABLE_ACCOUNT_HISTORY).append(" where username = ?");
        Cursor cursor = db.rawQuery(query.toString(),new String[]{username});
        if(cursor.moveToFirst()) {
            int id = cursor.getInt(0);
            cursor.close();
            /**如果该账号存在,更新*/
            ContentValues cv = new ContentValues();
            cv.put("username", username);
            cv.put("password", password);
            cv.put("headimgurl",img);
            int update = db.update(TABLE_ACCOUNT_HISTORY, cv, "id=?", new String[]{String.valueOf(id)});
            if (update != -1) {
                Log.i(TAG, "update " + username + " success");
            }
        }else {
            cursor.close();
            /**新增*/
            ContentValues cv = new ContentValues();
            cv.put("username", username);
            cv.put("password", password);
            cv.put("headimgurl",img);
            long insert = db.insert(TABLE_ACCOUNT_HISTORY,null,cv);
            if(insert !=-1) {
                Log.i(TAG,"insert "+insert +" success");
            }
        }
    }

    /**输入账号的时候搜索历史登录记录表*/
    public List<Account> onTextChange(String input){
        try{
            StringBuilder query = new StringBuilder();
            query.append("select * from ").append(TABLE_ACCOUNT_HISTORY).append(" where username like ?");
            Cursor cursor = getReadableDatabase().rawQuery(query.toString(),new String[]{input+"%"});

            List<Account> list = new ArrayList<>(cursor.getCount());
            while (cursor.moveToNext()){
                Account account = new Account();
                account.id = cursor.getInt(0);
                account.username = cursor.getString(1);
                account.password = cursor.getString(2);
                account.headimgurl = cursor.getString(3);
                list.add(account);
            }
            cursor.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    public List<Account> accounts(){
        try{
            StringBuilder query = new StringBuilder();
            query.append("select * from ").append(TABLE_ACCOUNT_HISTORY);
            Cursor cursor = getReadableDatabase().rawQuery(query.toString(),new String[]{});
            List<Account> list = new ArrayList<>(cursor.getCount());
            while (cursor.moveToNext()){
                Account account = new Account();
                account.id = cursor.getInt(0);
                account.username = cursor.getString(1);
                account.password = cursor.getString(2);
                account.headimgurl = cursor.getString(3);
                list.add(account);
            }
            cursor.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    public void delete(int id){
        try {
            SQLiteDatabase db = getWritableDatabase();
            db.delete(TABLE_ACCOUNT_HISTORY,"id=?",new String[]{String.valueOf(id)});
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    public void testInsert(){
        SQLiteDatabase db = getWritableDatabase();

        if(db.isDbLockedByCurrentThread()) {
            testInsert(db);
        }else {
            db.beginTransaction();
            try {
                testInsert(db);
                db.setTransactionSuccessful();
            }finally {
                db.endTransaction();
            }
        }
    }

    private void testInsert(SQLiteDatabase db){
//        for (int i = 0;i<15;i++){
//            ContentValues cv = new ContentValues();
//            cv.put("keyword",String.format("创维%s",i));
//            cv.put("time",System.currentTimeMillis());
//            db.insert(TABLE_SEARCH_HISTORY,null,cv);
//        }

        for (int i = 0;i<10;i++){
            ContentValues cv = new ContentValues();
            cv.put("username",String.format("1501840235%s",i));
            cv.put("password","lin123456");
            cv.put("headimgurl","https://ss3.bdstatic.com/70cFv8Sh_Q1YnxGkpoWK1HF6hhy/it/u=1646035102,3396193551&fm=27&gp=0.jpg");
            db.insert(TABLE_ACCOUNT_HISTORY,null,cv);
        }

    }
}
